﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

namespace DAL
{
    public class DBHelper
    {
        //private static string DBConnectString = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
        private static string DBConnectString = "Data Source=.;Initial Catalog=PlayerDB;Integrated Security=True";
        //private static string DBConnectString = "Data Source=.;Initial Catalog=asp.net6fx;Integrated Security=True;";
        private static SqlConnection conn;
        private static SqlDataAdapter da;
        private static SqlDataReader dr;
        private static SqlCommand cmd;
        private static DBHelper dBHelper;

        public DBHelper()
        {
            conn = new SqlConnection(DBConnectString);
        }

        public static DBHelper Instance()
        {
            if (dBHelper == null)
            {
                dBHelper = new DBHelper();
            }
            return dBHelper;
        }

        void DBOpen()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        void DBClose()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行查询（SELECT）
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>包含数据库数据的DataTable对象</returns>
        public DataTable GetDataTableBySql(string sql)
        {
            DBOpen();
            DataTable dt = new DataTable();
            da = new SqlDataAdapter(sql, conn);
            try
            {
                da.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                DBClose();
            }
        }

        /// <summary>
        /// 执行查询（SELECT）
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>用于检索数据库数据的读取器SqlDataReader对象</returns>
        public SqlDataReader GetDataReaderBySql(string sql)
        {
            DBOpen();
            cmd = new SqlCommand(sql, conn);
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 执行增、删、改（INSERT、DELETE、UPDATE）
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>操作成功与否</returns>
        public bool ExcuteSql(string sql)
        {
            DBOpen();
            cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                DBClose();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="proName">存储过程名</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>操作成功与否</returns>
        public bool ExcuteProcedure(string proName, SqlParameter[] paras)
        {
            DBOpen();
            cmd = new SqlCommand(proName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                cmd.Parameters.Add(paras[i]);
            }
            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                DBClose();
            }
        }

    }
}
